class Connect {
  static getInstance() {
      if (!Connect.instance) {
          Connect.instance = new Connect();
      }
      return Connect.instance;
  }

  constructor() {
      this.mysql = require('mysql');
      this.connection = this.mysql.createConnection({
        host     : 'localhost',
        user     : 'root',
        password : '11111111',
        database : 'stk_notes'
      });
      this.connection.connect();
  }

  async query(sql) {
      //有些查询语句比较特殊,需要自己写
      // sql:String
      let [err,res]=await this.errorCaptured(this._operation.bind(this),sql)
      return [err,res];
  }

  async errorCaptured(asyncFunc,sql) { 
    try {
      let res = await asyncFunc(sql)
      return [null,res]
    } catch (e) {
      return [e,null]
     }
  }

  async selectAll(array, table, where, link) {
      // array = Array
      // table = String
      // where = { key: value }
      // link = 'AND' or 'OR' default 'AND'
      let sql = "SELECT ";
      array.forEach(((value, index) => {
          if (index === 0) {
              sql += value;
          } else {
              sql += ',' + value
          }
      }));
      sql += ' FROM ' + table;
      if (where) {
          sql += this._handleWhereString(where, link);
      }
      let [err,res]=await this.errorCaptured(this._operation.bind(this),sql)
      return [err,res];
  }
  async selectOne(array, table, where, link) { 
    //其实selectOne和select只有最后return时有区别
      // array = Array
      // table = String
      // where = { key: value }
      // link = 'AND' or 'OR' default 'AND'
      let sql = "SELECT ";
      array.forEach(((value, index) => {
          if (index === 0) {
              sql += value;
          } else {
              sql += ',' + value
          }
      }));
      sql += ' FROM ' + table;
      if (where) {
          sql += this._handleWhereString(where, link);
      }
      let [err,res]=await this.errorCaptured(this._operation.bind(this),sql)
      return [err,res[0]];
  }

  async insert(info, table) {
      // info = { key: value }
      // table = String
      let sql = "INSERT INTO " + table + "(";
      let keyArray = [];
      let valueArray = [];
      Object.keys(info).forEach((key) => {
          keyArray.push(key);
          valueArray.push("'" + info[key] + "'");
      });
      let keyStr = keyArray.join(',');
      let valueStr = valueArray.join(',');
      sql += keyStr + ') ';
      sql += 'VALUES(' + valueStr + ')';
      let [err,res]=await this.errorCaptured(this._operation.bind(this),sql)
      return [err,res];
  }

  async update(info, table, where, link) {
      let sql = "UPDATE " + table + " SET ";
      let sqlArray = [];
    Object.keys(info).forEach((key) => {
          sqlArray.push(key + "='" + info[key] + "'");
      });
      sql += sqlArray.join(',');
      if (where) {
          sql += this._handleWhereString(where, link);
      }
    // return this._operation(sql);
    let [err,res]=await this.errorCaptured(this._operation.bind(this),sql)
    return [err,res];
  }

  delete(table, where, link) {
      // table = String
      // where = { key: value }
      // link = 'AND' or 'OR' default 'AND'
      let sql = "DELETE FROM " + table;
      if (where) {
          sql += this._handleWhereString(where, link);
      }
      return this._operation(sql);
  }

  async findOrCreate(array, table, where, defaults, link) { 
    //array 你要查找的字段
    //table 数据表
    //where 对象形式,作为查询的筛选条件
    //defaults 对象形式,如果发生create的情况,则作为插入的值
    //link 默认为and,可以写'or',sql语句where的后面
    let [err, res] = await this.selectAll(array, table, where, link)
    if (err) { return [err, res]  }
    if (res.length>0) {
      return [err, res]
    } else { 
    //没有数据,那就insert数据
      let _insertval = Object.assign({},where,defaults)
      var sss = await this.insert(_insertval, table)
      return sss
    }
    
  }

  _operation(sql) {
    return new Promise((resolve, reject) => {
      console.log('=====这里有什么=sql',sql);
      this.connection.query(sql, (error, result, fields) => {
            if (error) {
              console.log('===from dbIns.js的console的===sql报错解释:', error.sqlMessage,'===报错语句:===',error.sql);
              reject(error.sqlMessage);
            } else {
              resolve(result);
              }
          });
      })
  }
  _handleWhereString(where, link) {
      let str = "";
      let whereArray = [];
      Object.keys(where).forEach((key) => {
          whereArray.push(String(key + "='" + where[key] + "'"));
      });
      if (link) {
          let whereStr = whereArray.join(" " + link + " ");
          str += " WHERE " + whereStr;
      } else {
          let whereStr = whereArray.join(" AND ");
          str += " WHERE " + whereStr;
      }
      return str;
  }
}

module.exports = Connect.getInstance();